package com.daiqiu.manager.controller;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.daiqiu.manager.util.GetSource;
import com.daiqiu.manager.util.SnowflakeIdWorker;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import java.math.BigDecimal;
import java.sql.*;

@Controller
public class ManagerController {

    DruidPooledConnection con = new GetSource().getConnection();

    @Autowired
    SnowflakeIdWorker idWorker;


    /**
     * 返回产品页数
     * @param flag
     * @return
     * @throws SQLException
     */
    @RequestMapping("getPageIndex")
    @ResponseBody
    public int getPageIndex(int flag) throws SQLException {
        //获取保险的所有信息
        String searchInsurance="SELECT * FROM t_insurance";
        //获取基金的所有信息
        String searchFund="SELECT * FROM t_fund";
        //获取股票的所有信息
        String searchStock="SELECT * FROM t_stock";
        int rowNum=0;
        if(flag==0){
            PreparedStatement preparedStatement= con.prepareStatement(searchInsurance, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet resultSet=preparedStatement.executeQuery();
            resultSet.last();
            rowNum=resultSet.getRow();
            resultSet.beforeFirst();
            resultSet.close();
        }
        else if(flag==1){
                PreparedStatement preparedStatement= con.prepareStatement(searchFund, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                ResultSet resultSet=preparedStatement.executeQuery();
                resultSet.last();
                rowNum=resultSet.getRow();
                resultSet.beforeFirst();
                resultSet.close();
        }
        else if(flag==2){
            PreparedStatement preparedStatement= con.prepareStatement(searchStock, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet resultSet=preparedStatement.executeQuery();
            resultSet.last();
            rowNum=resultSet.getRow();
            resultSet.beforeFirst();
            resultSet.close();
        }
        if(rowNum==0) return 0;
        else if(rowNum%10==0) return rowNum/10;
        else return rowNum/10+1;
    }

    /**
     * 返回特定产品所有数据
     * @param flag
     * @return
     */
    @RequestMapping("/showMessage")
    @ResponseBody
    public String[][] showMessage(int flag,int pageIndex) throws SQLException {
        //获取保险的所有信息
        String searchInsurance="SELECT * FROM t_insurance";
        //获取基金的所有信息
        String searchFund="SELECT * FROM t_fund";
        //获取股票的所有信息
        String searchStock="SELECT * FROM t_stock";
        int maxLength=10;
        pageIndex++;
        String[][] res=null;
        if(flag==0) {
            PreparedStatement preparedStatement= con.prepareStatement(searchInsurance, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet resultSet=preparedStatement.executeQuery();
            resultSet.last();
            int rowNum=resultSet.getRow();
            resultSet.beforeFirst();
            if(rowNum==0)
                res=new String[0][7];
            else if(pageIndex*10>rowNum)
                res=new String[rowNum%10][7];
            else
                res=new String[10][7];
            int count=0;
            int index=(pageIndex-1)*10;
            resultSet.absolute(index);
            while(resultSet.next()&&count<10){
//                if(count > row) return res;
                for(int i=1;i<=5;i++){
                    res[count][i-1]=resultSet.getString(i);
                    if(i==4){
                        int length=res[count][i-1].length();
                        if(length>=maxLength){
                            res[count][i-1]=res[count][i-1].substring(0,maxLength)+"...";
                        }
                        else{
                            while(res[count][i-1].length()<maxLength){
                                res[count][i-1]+=" ";
                            }
                        }
                    }
                }
                if(resultSet.getString(6).equals("1")){
                    res[count][5]="在售";
                    res[count][6]="0";
                }
                else{
                    res[count][5]="不在售";
                    res[count][6]="1";
                }
                count++;
            }
            resultSet.close();
        }
        //flag==1 获取所有基金的信息
        else if(flag==1){
            PreparedStatement preparedStatement= con.prepareStatement(searchFund, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet resultSet=preparedStatement.executeQuery();
            resultSet.last();
            int rowNum=resultSet.getRow();
            resultSet.beforeFirst();

            if(rowNum==0)
                res=new String[0][9];
            else if(pageIndex*10>rowNum)
                res=new String[rowNum%10][9];
            else
                res=new String[10][9];
            int count=0;
            int index=(pageIndex-1)*10;
            resultSet.absolute(index);
            while(resultSet.next()&&count<10){
                for(int i=1;i<=8;i++){
                    res[count][i-1]=resultSet.getString(i);
                    if(i==7){
                        if(res[count][i-1].length()>=maxLength){
                            res[count][i-1]=res[count][i-1].substring(0,maxLength)+"...";
                        }
                        else{
                            while(res[count][i-1].length()<maxLength){
                                res[count][i-1]+=" ";
                            }
                        }
                    }
                }

                if(resultSet.getString(6).equals("1")){
                    res[count][5]="在售";
                    res[count][8]="0";
                }
                else{
                    res[count][5]="不在售";
                    res[count][8]="1";
                }
                count++;
            }
            resultSet.close();
        }
        //flag==2 获取所有股票的信息
        else if(flag==2){
            PreparedStatement preparedStatement= con.prepareStatement(searchStock, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ResultSet resultSet=preparedStatement.executeQuery();
            resultSet.last();
            int rowNum=resultSet.getRow();
            resultSet.beforeFirst();
            if(rowNum==0)
                res=new String[0][8];
            else if(pageIndex*10>rowNum)
                res=new String[rowNum%10][8];
            else
                res=new String[10][8];
            int count=0;
            int index=(pageIndex-1)*10;
            resultSet.absolute(index);
            while(resultSet.next()&&count<10){
                for(int i=1;i<=7;i++){
                    res[count][i-1]=resultSet.getString(i);
                    if(i==3){
                        if(res[count][i-1].length()>=maxLength){
                            res[count][i-1]=res[count][i-1].substring(0,maxLength)+"...";
                        }
                        else{
                            while(res[count][i-1].length()<maxLength){
                                res[count][i-1]+=" ";
                            }
                        }
                    }
                }

                if(resultSet.getString(5).equals("1")){
                    res[count][4]="在售";
                    res[count][7]="0";
                }
                else{
                    res[count][4]="不在售";
                    res[count][7]="1";
                }
                count++;
            }
            resultSet.close();
        }

        return res;
    }


    /**
     * 添加产品
     * @param message
     * @return
     */
    @RequestMapping("/addProduct")
    @ResponseBody
    public boolean addProduct(int flag, @RequestParam(value = "message[]") String[] message) {
        //增加保险
        String insertInsurance="INSERT INTO t_insurance(in_id,in_name,in_price,in_description,in_type,in_status) VALUES(?,?,?,?,?,?)";
        //增加基金
        String insertFund="INSERT INTO t_fund(fu_id,fu_name,fu_type,fu_risk,fu_manager,fu_status,fu_description,fu_price) VALUES(?,?,?,?,?,?,?,?)";
        //增加股票
        String insertStock="INSERT INTO t_stock(st_id,st_name,st_description,st_price,st_status,st_risk,st_type) VALUES (?,?,?,?,?,?,?)";
        try {
            //flag==0 插入保险的相关信息
            if(flag==0){
//                description=message[2];
//                if(description.length()>=maxLength){
//                    description=description.substring(0,maxLength);
//                    description+="...";
//                }
//                else {
//                    int gap=10-description.length();
//                    for(int i=0;i<gap;i++){
//                        description+=" ";
//                    }
//                }
                PreparedStatement preparedStatement=con.prepareStatement(insertInsurance);
                preparedStatement.setString(1,idWorker.nextId()+"33");
                preparedStatement.setString(2,message[0]);
                preparedStatement.setBigDecimal(3,new BigDecimal(message[1]));
                preparedStatement.setString(4,message[2]);
                preparedStatement.setString(5,message[3]);
                preparedStatement.setBoolean(6,false);
                preparedStatement.execute();
            }
            //flag==1 插入基金相关信息
            else if(flag==1){
//                description=message[4];
//                if(description.length()>=maxLength) {
//                    description=description.substring(0,maxLength);
//                    description+="...";
//                }
//                else {
//                    int gap=10-description.length();
//                    for(int i=0;i<gap;i++){
//                        description+=" ";
//                    }
//                }
                PreparedStatement preparedStatement=con.prepareStatement(insertFund);
                preparedStatement.setString(1,idWorker.nextId()+"22");
                preparedStatement.setString(2,message[0]);
                preparedStatement.setString(3,message[1]);
                preparedStatement.setInt(4,Integer.parseInt(message[2]));
                preparedStatement.setString(5,message[3]);
                preparedStatement.setBoolean(6,false);
                preparedStatement.setString(7,message[4]);
                preparedStatement.setBigDecimal(8,new BigDecimal(message[5]));
                preparedStatement.execute();
            }
            //flag==2 插入股票的相关信息
            else if(flag==2){
//                description=message[1];
//                if(description.length()>=maxLength){
//                    description=description.substring(0,maxLength);
//                    description+="...";
//                }
//                else {
//                    int gap=10-description.length();
//                    for(int i=0;i<gap;i++){
//                        description+=" ";
//                    }
//                }
                PreparedStatement preparedStatement=con.prepareStatement(insertStock);
                preparedStatement.setString(1,idWorker.nextId()+"11");
                preparedStatement.setString(2,message[0]);
                preparedStatement.setString(3,message[1]);
                preparedStatement.setBigDecimal(4,new BigDecimal(message[2]));
                preparedStatement.setBoolean(5,false);
                preparedStatement.setInt(6,Integer.parseInt(message[3]));
                preparedStatement.setString(7,message[4]);
                preparedStatement.execute();

            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }


    /**
     * 上线产品
     * @param flag
     * @param id
     * @return
     */
    @RequestMapping("/upProduct")
    @ResponseBody
    public boolean upProduct(int flag, String id) {
        //查找要上线的保险的条目
        String upInsurance="SELECT * FROM t_insurance WHERE in_id=?";
        //查找要上线的基金的条目
        String upFund="SELECT * FROM t_fund WHERE fu_id=?";
        //查找要上线的股票的条目
        String upStock="SELECT * FROM t_stock WHERE st_id=?";
        try {
            if(flag==0){
                PreparedStatement preparedStatement=con.prepareStatement(upInsurance, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                preparedStatement.setString(1,id);
                ResultSet resultSet=preparedStatement.executeQuery();
                //若没有该产品的记录，则返回false
                if(resultSet.isBeforeFirst()==false){
                    return false;
                }
                //对该产品记录进行修改
                resultSet.next();
                if(resultSet.getBoolean(6)) return false;
                resultSet.updateBoolean(6,true);
                resultSet.updateRow();
            }
            else if(flag==1){
                PreparedStatement preparedStatement=con.prepareStatement(upFund, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                preparedStatement.setString(1,id);
                ResultSet resultSet=preparedStatement.executeQuery();
                //若没有该产品的记录，则返回false
                if(resultSet.isBeforeFirst()==false){
                    return false;
                }
                resultSet.next();
                if(resultSet.getBoolean(6)) return false;
                resultSet.updateBoolean(6,true);
                resultSet.updateRow();
            }
            else if(flag==2){
                PreparedStatement preparedStatement=con.prepareStatement(upStock, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                preparedStatement.setString(1,id);
                ResultSet resultSet=preparedStatement.executeQuery();
                //若没有该产品的记录，则返回false
                if(resultSet.isBeforeFirst()==false){
                    return false;
                }
                resultSet.next();
                if(resultSet.getBoolean(5)) return false;
                resultSet.updateBoolean(5,true);
                resultSet.updateRow();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }


    /**
     * 下线产品
     * @param flag
     * @param id
     * @return
     */
    @RequestMapping("/downProduct")
    @ResponseBody
    public boolean downProduct(int flag, String id) {
        //查找要下线的保险的条目
        String downInsurance="SELECT * FROM t_insurance WHERE in_id=?";
        //查找要下线的基金的条目
        String downFund="SELECT * FROM t_fund WHERE fu_id=?";
        //查找要下线的股票的条目
        String downStock="SELECT * FROM t_stock WHERE st_id=?";
        try {
            if(flag==0){
                PreparedStatement preparedStatement=con.prepareStatement(downInsurance, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                preparedStatement.setString(1,id);
                ResultSet resultSet=preparedStatement.executeQuery();
                //若没有该产品的记录，则返回false
                if(resultSet.isBeforeFirst()==false){
                    return false;
                }
                //对该产品记录进行修改
                resultSet.next();
                if(!resultSet.getBoolean(6)) return false;
                resultSet.updateBoolean(6,false);
                resultSet.updateRow();
            }
            else if(flag==1){
                PreparedStatement preparedStatement=con.prepareStatement(downFund, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                preparedStatement.setString(1,id);
                ResultSet resultSet=preparedStatement.executeQuery();
                //若没有该产品的记录，则返回false
                if(resultSet.isBeforeFirst()==false){
                    return false;
                }
                resultSet.next();
                if(!resultSet.getBoolean(6)) return false;
                resultSet.updateBoolean(6,false);
                resultSet.updateRow();
            }
            else if(flag==2){
                PreparedStatement preparedStatement=con.prepareStatement(downStock, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
                preparedStatement.setString(1,id);
                ResultSet resultSet=preparedStatement.executeQuery();
                //若没有该产品的记录，则返回false
                if(resultSet.isBeforeFirst()==false){
                    return false;
                }
                resultSet.next();
                if(!resultSet.getBoolean(5)) return false;
                resultSet.updateBoolean(5,false);
                resultSet.updateRow();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }


}
